Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Xml
Imports System.Data.SQLite
Imports System.IO
Imports Newtonsoft.Json
Imports Newtonsoft.Json.Linq

Public Class SiteMapBLL

    Public Shared Function GetListForSiteMap(_name As String, lang As String) As DataTable

        ReadJSON()

        Dim dataSet As DataSet

        Dim doc As New XmlDocument()
        doc.Load(HttpContext.Current.Server.MapPath("/App_Data/UserCategories.xml"))

        ' Create a new DataTable. 
        Dim table As DataTable = New DataTable("CatTable")

        ' Declare variables for DataColumn and DataRow objects. 
        Dim column As DataColumn
        Dim row As DataRow

        ' Create new DataColumn, set DataType, ColumnName  
        ' and add to DataTable.    
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.Int32")
        column.ColumnName = "ID"
        column.ReadOnly = True
        column.Unique = True

        ' Add the Column to the DataColumnCollection.
        table.Columns.Add(column)

        ' Create next column.
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "Title"
        column.AutoIncrement = False
        column.Caption = "Title"
        column.ReadOnly = False
        column.Unique = False

        ' Add the column to the table.
        table.Columns.Add(column)

        ' Create next column.
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "CatType"
        column.AutoIncrement = False
        column.Caption = "CatType"
        column.ReadOnly = False
        column.Unique = False

        ' Add the column to the table.
        table.Columns.Add(column)

        ' Create next column.
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "Url"
        column.AutoIncrement = False
        column.Caption = "Url"
        column.ReadOnly = False
        column.Unique = False

        ' Add the column to the table.
        table.Columns.Add(column)

        ' Create next column.
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "Parentstr"
        column.AutoIncrement = False
        column.Caption = "Parentstr"
        column.ReadOnly = False
        column.Unique = False

        ' Add the column to the table.
        table.Columns.Add(column)

        ' Make the ID column the primary key column. 
        Dim PrimaryKeyColumns(0) As DataColumn
        PrimaryKeyColumns(0) = table.Columns("id")
        table.PrimaryKey = PrimaryKeyColumns

        ' Instantiate the DataSet variable.
        DataSet = New DataSet()

        ' Add the new DataTable to the DataSet.
        DataSet.Tables.Add(table)

        row = table.NewRow()
        row("ID") = 0
        row("Title") = "Home"
        row("CatType") = "rootcat"
        row("Url") = "Browse/"
        row("Parentstr") = ""
        table.Rows.Add(row)

        Dim root As XmlNode = doc.FirstChild
        Dim i As Integer
        Dim _CatType As String = ""
        Dim str As String = ConfigurationManager.AppSettings("CatTypes").ToString
        Dim _CatTypes() As String = str.Split(",")

        'Display the contents of the child nodes. 
        If root.HasChildNodes Then
            For i = 0 To root.ChildNodes.Count - 1
                'Response.Write(root.ChildNodes(i).InnerText & "<br>")
                'Response.Write(root.ChildNodes(i).LocalName & "<br>") 
                row = table.NewRow()
                row("ID") = i + 1

                For Each c As String In _CatTypes
                    If InStr(root.ChildNodes(i).InnerText, c) > 0 Then
                        _CatType = c
                    End If
                Next

                Dim CatArray() As String = Split(root.ChildNodes(i).InnerText, _CatType)
                row("Title") = Replace(CatArray(0).ToString(), "..", " ")
                row("CatType") = _CatType
                Dim tablename As String = _CatType
                If _CatType = "publisher" Then tablename = "publishers"
                Dim Id As String = LibCalibreVB.ExecuteScalar("Select Id FROM " & tablename & " WHERE name = '" & Replace(CatArray(0).ToString(), "..", " ") & "'")
                'row("Url") = "Browse.aspx?c=" & _CatType & "&id=" & Id
                row("Url") = "/List/" & _CatType & "/" & Id
                row("Parentstr") = Replace(root.ChildNodes(i).LocalName.ToString, "..", " ")
                table.Rows.Add(row)
            Next i
        End If

        Dim view As New DataView(table)
        Dim distinctValues As DataTable = view.ToTable(True, "Parentstr")
        Dim foundRows() As DataRow
        ' Use the Select method to find all rows.
        foundRows = distinctValues.Select()

        Dim y As Integer
        Dim position As Integer
        Dim cat As String
        Dim catparent As String
        ' Print column 0 of each returned row. 
        For y = 1 To foundRows.GetUpperBound(0)
            cat = foundRows(y)(0)
            position = InStrRev(cat, ".", -1, vbTextCompare)
            If position > 0 Then
                catparent = Left(cat, position - 1)
            Else
                catparent = "Home"
            End If

            row = table.NewRow()
            i = i + 1
            row("ID") = i
            row("Title") = cat
            row("CatType") = "usercat"
            'row("Url") = "Default.aspx?c=usercat" & "&id=" & i
            row("Url") = "Browse/usercat/" & i
            row("Parentstr") = catparent
            table.Rows.Add(row)
        Next y

        str = ConfigurationManager.AppSettings("CatRoot").ToString
        Dim _CatRoot() As String = str.Split(",")
        For Each _cat As String In _CatRoot
            row = table.NewRow()
            i = i + 1
            row("ID") = i
            row("Title") = StrConv(_cat, VbStrConv.ProperCase)
            row("CatType") = "calcat"
            row("Url") = "Browse/" & _cat
            row("Parentstr") = "Home"
            table.Rows.Add(row)
        Next

        ' Create next column.
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.Int32")
        column.ColumnName = "Parent"
        column.AutoIncrement = False
        column.Caption = "Parent"
        column.ReadOnly = False
        column.Unique = False

        ' Add the column to the table.
        table.Columns.Add(column)

        Dim trows As Integer = table.Rows.Count
        For trows = 0 To table.Rows.Count - 1
            Dim parentvalue As String = table.Rows(trows)("Parentstr")
            Dim expression As String = "Title = '" & parentvalue & "'"
            Dim foundParentID() As DataRow
            foundParentID = table.Select(expression)

            Dim crows As Integer
            ' Print column 0 of each returned row. 
            For crows = 0 To foundParentID.GetUpperBound(0)
                table.Rows(trows)("Parent") = foundParentID(crows)(0)
            Next crows
        Next

        Return table

    End Function

    Public Shared Function ReadJSON() As Object

        Dim commandText As String = "SELECT val FROM preferences Where key = 'user_categories'"
        Dim json As String = LibCalibreVB.ExecuteScalar(commandText)
        Dim jObj As JObject = JObject.Parse(json)
        json = jObj.ToString(Newtonsoft.Json.Formatting.None)

        json = Replace(json, " ", "..")


        Try
            Dim doc As XmlDocument = New XmlDocument()
            doc = DirectCast(JsonConvert.DeserializeXmlNode(json, "rootcat"), XmlDocument)

            Dim writer As XmlTextWriter = New XmlTextWriter(HttpContext.Current.Server.MapPath("/App_Data/UserCategories-input.xml"), Nothing)
            writer.Formatting = System.Xml.Formatting.Indented
            doc.Save(writer)
            writer.Close()

            Dim arrText() As String

            arrText = File.ReadAllLines(HttpContext.Current.Server.MapPath("/App_Data/UserCategories-input.xml"))

            Using objWriter As StreamWriter = New StreamWriter(HttpContext.Current.Server.MapPath("/App_Data/UserCategories.xml"))
                For I As Integer = 1 To arrText.Length - 1
                    objWriter.WriteLine(arrText(I))
                Next
            End Using

        Catch ex As Exception
            HttpContext.Current.Response.Write(ex.ToString)
        End Try

        Return Nothing
    End Function


End Class
